﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using 纺织贸易管理系统;

namespace 后整理管理系统.自定义类
{
    public class QueryHelper
    {
        /// <summary>
        /// 获取入库单据分页数据
        /// </summary>
        public static  DataTable GetInboundDataPage(DateTime startDate, DateTime endDate,
            string kehu, string pingming, string ordernum, string ganghao, string yanse,
            int pageIndex, int pageSize, ref int totalCount)
        {
            // 构建分页SQL查询
            string sql = @"
            WITH MainData AS (
                SELECT 
                    x.id,
                    y.ID AS ID1,
                    y.pingming,
                    x.dh,
                    x.bz,
                    x.ksmc,
                    x.ckmc,
                    y.CustomName,
                    x.own,
                    x.jiagongleixing,
                    x.fromDanhao,
                    y.toupimishu AS toupimishu1,
                    y.danwei,
                    y.Bianhao,
                    y.toupijuanshu AS toupijuanshu1,
                    y.ExitsNum,
                    y.ExitsRoll,
                    y.Kuwei,
                    y.Rangchang,
                    y.offerid,
                    y.danhao,
                    y.ganghao,
                    y.yanse,
                    y.beizhu,
                    y.OrderNum AS OrderNum1,
                    y.houzhengli,
                    y.suolv AS suolv1,
                    y.UpRolledNum,
                    y.UpRolledRolls,
                    y.InventoryQuantity,
                    y.kuanhao,
                    y.OutboundUnit,
                    y.ProducedQuantity,
                    y.chengpingmishu,
                    y.chengpingjuanshu,
                    y.FabricStyle,
                    y.RollUpShrinkageRate,
                    x.rq,
                    x.Fahuodizhi,
                    y.PreSaleQuantity AS 预出库数量,
                    y.PreSaleRolls AS 预出库卷数,
                    y.PreThumbnail AS 预出库缩率,
                    y.InventoryRolls AS 库存卷数
                FROM DanjuTable x
                INNER JOIN danjumingxitable y ON x.dh = y.danhao
                WHERE x.rq >= @startDate
                    AND x.rq <= DATEADD(DAY, 1, @endDate)
                    AND x.djlx = '入库单'
                    AND x.ksmc LIKE '%' + @kehu + '%'
                    AND y.pingming LIKE '%' + @pingming + '%'
                    AND y.OrderNum LIKE '%' + @ordernum + '%'
                    AND y.ganghao LIKE '%' + @ganghao + '%'
                    AND y.yanse LIKE '%' + @yanse + '%'
            ),
          
          
            OFFSET (@pageIndex - 1) * @pageSize ROWS
            FETCH NEXT @pageSize ROWS ONLY";

            var parameters = new
            {
                startDate = startDate.Date,
                endDate = endDate.Date,
                kehu = kehu ?? "",
                pingming = pingming ?? "",
                ordernum = ordernum ?? "",
                ganghao = ganghao ?? "",
                yanse = yanse ?? "",
                pageIndex = pageIndex,
                pageSize = pageSize
            };

            // 执行查询
            var result =Connect.dbcontext.Ado.GetDataTable(sql, parameters);

            // 从结果中提取总记录数
            if (result.Rows.Count > 0)
            {
                totalCount = Convert.ToInt32(result.Rows[0]["TotalCount"]);

                // 移除TotalCount列
                result.Columns.Remove("TotalCount");
            }

            return result;
        }

    }
}
